import elixir
from elixir import *
from db import *
from datetime import datetime
from sqlalchemy import and_, or_

t_begin = datetime(1,1,1)
t_infy = datetime(9999, 12, 31)

class Stock(Entity):
    has_field("symbol", Unicode(30), unique=True, primary_key=True)
    has_field("name", Unicode(30))
    has_field("sector", Unicode(30))
    has_field("first_purchase_date", DateTime, nullable=False, default=datetime.now)
    has_field("stoploss", Integer)
    has_field("notes", String)
    has_many("prices", of_kind="Price")
    has_many("transactions", of_kind="Transaction")
    has_one("wish", of_kind="WishList")
    using_options(tablename="stocks")

    def _getHighestPrice(self):
        if self.prices:
            return max ( [price.price for price in self.prices if price.date > self.first_purchase_date] )
        return 0

    def _getCurrentPrice(self):
        if self.prices:
            return self.prices[-1].price
        return 0

    def _suggest(self):
        if not self.transactions: return "-"
        diff = 100 - ((self.current_price / self.highest_price) * 100)
        if diff >= self.stoploss:
            return "SELL"
        return "HOLD"

    def _getQuantity(self):
        return sum( [tr.quantity for tr in self.transactions] )

    def getRProfit(self, start_dt=t_begin, end_dt=t_infy):
        trs = sorted([tr for tr in self.transactions if tr.tr_type=="sell" and start_dt < tr.date < end_dt], key = lambda tr: tr.date)
        if trs:
            return trs[-1].profit
        return 0

    def _getURProfit(self):
        if self.transactions:
            return self.quantity * (self.current_price - self.transactions[-1].avg_price)
        return 0

    highest_price = property(_getHighestPrice)
    current_price = property(_getCurrentPrice)
    suggestion = property(_suggest)
    quantity = property(_getQuantity)
    r_profit = property(getRProfit)
    ur_profit = property(_getURProfit)

class Price(Entity):
    has_field("date", DateTime, nullable=False, default=datetime.now)
    has_field("price", Float)
    belongs_to("stock", of_kind="Stock")#, colname='symbol',)
    using_options(tablename="prices")

class Transaction(Entity):
    has_field("tr_type", String(5))
    has_field("date", DateTime)
    has_field("quantity", Integer, default=0)
    has_field("price", Float) # cost_per_share  float,
    belongs_to("stock", of_kind="Stock")#, colname="symbol")
    using_options(tablename="transactions")
    
    def _compute_profit(self):
        profit = 0
        if self.tr_type == "sell":
            profit = self.price + (self.avg_price * self.quantity)
        return profit

    def _get_avgprice(self):
        quantity = 0
        worth = 0
        avg_price = 0
        for tr in Transaction.query.filter(and_(Transaction.stock==self.stock, Transaction.tr_type=="buy")):
            if tr.date > self.date: continue
            quantity += tr.quantity
            worth += tr.price
        if worth and quantity:
            avg_price = float (worth) / quantity
        return avg_price

    avg_price = property(_get_avgprice)
    profit = property(_compute_profit)

class WishList(Entity):
    has_field("target_price", Float)
    belongs_to("stock", of_kind="Stock")#, colname="symbol")
    using_options(tablename="wishlist")

    def _suggest(self):
        if self.stock.current_price <= self.target_price:
            return "BUY"
        return ""

    suggestion = property(_suggest)

setup_all()
create_all()
commit()
